package com.xiaoxu.intranetweb.mybatisplusSzappdb.mapper;

import com.xiaoxu.intranetweb.mybatisplusSzappdb.bo.*;
import com.xiaoxu.intranetweb.mybatisplusSzappdb.entity.CWeightSheets;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xiaoxu.intranetweb.mybatisplusSzappdb.vo.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author xiaoxu
 * @since 2022-12-27
 */
@Component
public interface CWeightSheetsMapper extends BaseMapper<CWeightSheets> {
    //    查询WEISHT_MONEY的总和，WEISHT_NET_WGHT总和，总条数*只查出货物
    @Select("select sum(WEISHT_MONEY) as totalMoney,sum(WEISHT_NET_WGHT) as totalWeight,count(*) as totalNum from C_WEIGHT_SHEETS where  WEISHT_PRODUCT NOT LIKE '%.%'  and WEISHT_DELETED = 0 and DATA_FROM = #{dataFrom}")
//    将结果放到CWeightSheetsTotalBo
    AggregateVo aggregate(@Param("dataFrom") String dataFrom);

    //    传入年份，按照WEISHT_PRODUCT品种分类，然后统计12个月的WEISHT_NET_WGHT总和*只查出货物
    @Select("select sum(WEISHT_NET_WGHT) as totalWeight,MONTH(WEISHT_FULL_TM) as month,WEISHT_PRODUCT as product from C_WEIGHT_SHEETS  where YEAR(WEISHT_FULL_TM) = #{year} AND WEISHT_PRODUCT NOT LIKE '%.%'  and WEISHT_DELETED = 0 and DATA_FROM = #{dataFrom} group by MONTH(WEISHT_FULL_TM),WEISHT_PRODUCT  ")
    List<MonthlyWeightTrendVo> monthlyWeightTrend(int year, @Param("dataFrom") String dataFrom);

    //    传入年份，按照WEISHT_PRODUCT品种分类，然后统计12个月的WEISHT_MONEY*只查出货物
    @Select("select sum(WEISHT_MONEY) as totalMoney,MONTH(WEISHT_FULL_TM) as month,WEISHT_PRODUCT as product from C_WEIGHT_SHEETS  where YEAR(WEISHT_FULL_TM) = #{year}  AND WEISHT_PRODUCT NOT LIKE '%.%'  and WEISHT_DELETED = 0 and DATA_FROM = #{dataFrom} group by MONTH(WEISHT_FULL_TM),WEISHT_PRODUCT ")
    List<MonthlyMoneyTrendVo> monthlyMoneyTrend(int year, @Param("dataFrom") String dataFrom);

    //    传入年份，各品种的年总重量Annual*只查出货物
    @Select("select sum(WEISHT_NET_WGHT) as totalWeight,WEISHT_PRODUCT as product from C_WEIGHT_SHEETS  where YEAR(WEISHT_FULL_TM) = #{year} AND WEISHT_PRODUCT NOT LIKE '%.%' and WEISHT_DELETED = 0 and DATA_FROM = #{dataFrom} group by WEISHT_PRODUCT ")
    List<AnnualWeightTrendVo> yearlyWeightTrend(int year, @Param("dataFrom") String dataFrom);

    //    传入年份，各品种的年总金额Annual*只查出货物
    @Select("select sum(WEISHT_MONEY) as totalMoney,WEISHT_PRODUCT as product from C_WEIGHT_SHEETS  where YEAR(WEISHT_FULL_TM) = #{year} AND WEISHT_PRODUCT NOT LIKE '%.%' and WEISHT_DELETED = 0 and DATA_FROM = #{dataFrom} group by WEISHT_PRODUCT ")
    List<AnnualMoneyTrendVo> yearlyMoneyTrend(int year, @Param("dataFrom") String dataFrom);
//    传入日期，按公司查询这期间每个品种的总重量，总金额，总条数
//    @Select("select sum(WEISHT_NET_WGHT) as totalWeight,sum(WEISHT_MONEY) as totalMoney,count(*) as totalNum,WEISHT_PRODUCT as product,WEISHT_CO as company from C_WEIGHT_SHEETS  where WEISHT_FULL_TM between #{startDate} and #{endDate}  and WEISHT_DELETED = 0 and DATA_FROM = #{dataFrom} group by WEISHT_CO,WEISHT_PRODUCT ")
//    List<CompanyWeightMoneyNumBo> productWeightMoneyNum(String startDate, String endDate,@Param("dataFrom") String dataFrom);

    List<CompanyWeightMoneyNumVo> companyWeightMoneyNum(@Param("s") CompanyWeightMoneyNumBo companyWeightMoneyNumBo);

    List<CompanyWeightMoneyNumVo> productWeightMoneyNum(@Param("s") CompanyWeightMoneyNumBo companyWeightMoneyNumBo);

    //    传入开始时间和结束时间查询某个单位时间区间内消费金额
    @Select("select sum(WEISHT_MONEY) as balanceConsume,WEISHT_CO as company from C_WEIGHT_SHEETS  where WEISHT_FULL_TM between #{startTime} and #{endTime} and WEISHT_CO = #{co}  and WEISHT_DELETED = 0 and DATA_FROM = #{dataFrom} group by WEISHT_CO ")
    GetBalanceEnquiryVo getBalanceEnquiry(String startTime, String endTime, String co, String chiCo, @Param("dataFrom") String dataFrom);


    //结束时间之前某个单位的所有消费金额（时间点止消费金额）
    @Select("select sum(WEISHT_MONEY) as balanceEnd,WEISHT_CO as company from C_WEIGHT_SHEETS  where WEISHT_FULL_TM <= #{endTime} and WEISHT_CO = #{co} and WEISHT_CHI_CO = #{chi} and  WEISHT_CO_SUPPLY = #{supplyCo} and WEISHT_CO_SUPPLY_CHI = #{supplyChiCo}  AND WEISHT_DELETED = 0 and DATA_FROM = #{dataFrom} ")
    GetBalanceEnquiryVo getBalanceEnd(String endTime, String co, String chi, String supplyCo, String supplyChiCo, @Param("dataFrom") String dataFrom);

    //结束时间之前某个单位的所有充值金额（时间点止充值金额）
    @Select("select sum(FINANCE_ENTER_NUM) as balanceEnd,FINANCE_ENTER_CO as company ,FINANCE_SUBJECT_ID from C_FINACE_ENTER  where FINANCE_ENTER_TIME <= #{endTime} and FINANCE_ENTER_CO = #{co} and FINANCE_ENTER_CHI_CO =  #{chi} AND FINANCE_SUBJECT_ID = #{subId} and FINANCE_ENTER_DELETED = '0' and FINANCE_ENTERED = 1 and DATA_FROM = #{dataFrom}")
    GetBalanceEnquiryVo getBalanceEnd2(String endTime, String co, String chi, String subId, @Param("dataFrom") String dataFrom);

//    查询计划区间内的拖运情况

    @Select("SELECT COUNT(*) AS count, SUM(WEISHT_NET_WGHT) AS sum\n" +
            "    FROM c_weight_sheets\n" +
            "    WHERE WEISHT_CO = #{cpyCo}\n" +
            "    AND WEISHT_CHI_CO = #{cpyChico}\n" +
            "    AND WEISHT_PRODUCT = #{pdtinfPdt}\n" +
            "    AND WEISHT_DELETED = 0 AND DATA_FROM = #{dataFrom} \n" +
            "    AND WEISHT_FULL_TM BETWEEN #{pdtPlanexBegin} AND #{pdtPlanexEnd} ")
    Map<String, Object> getPlanWeight(@Param("cpyCo") String cpyCo, @Param("cpyChico") String cpyChico, @Param("pdtinfPdt") String pdtinfPdt, @Param("pdtPlanexBegin") String pdtPlanexBegin, @Param("pdtPlanexEnd") String pdtPlanexEnd, @Param("dataFrom") String dataFrom);

    //查询每月的各品种销售情况
    @Select("SELECT \n" +
            "    EXTRACT(YEAR FROM c_weight_sheets.WEISHT_FULL_TM) AS year,\n" +
            "\t\t  EXTRACT(MONTH FROM c_weight_sheets.WEISHT_FULL_TM) AS month,\n" +
            "    c_pdt_class.PDTCLASS_CLASS AS product_class,\n" +
            "    c_products_info.PDTINF_PDT AS product,\n" +
            "    SUM(c_weight_sheets.WEISHT_NET_WGHT) AS total_weight,\n" +
            "    AVG(c_weight_sheets.WEISHT_PRICE) AS average_price,\n" +
            "    SUM(c_weight_sheets.WEISHT_MONEY) AS total_sales,\n" +
            "    SUM(SUM(c_weight_sheets.WEISHT_NET_WGHT)) OVER (PARTITION BY EXTRACT(YEAR FROM c_weight_sheets.WEISHT_FULL_TM)) AS yearWeight,\n" +
            "    AVG(AVG(c_weight_sheets.WEISHT_PRICE)) OVER (PARTITION BY EXTRACT(YEAR FROM c_weight_sheets.WEISHT_FULL_TM)) AS yearAveragePrice,\n" +
            "    SUM(SUM(c_weight_sheets.WEISHT_MONEY)) OVER (PARTITION BY EXTRACT(YEAR FROM c_weight_sheets.WEISHT_FULL_TM)) AS yearSales\n" +
            "FROM \n" +
            "    c_weight_sheets\n" +
            "JOIN \n" +
            "    c_products_info ON c_weight_sheets.WEISHT_PRODUCT = c_products_info.PDTINF_PDT\n" +
            "JOIN \n" +
            "    c_pdt_class ON c_products_info.PDTINF_CLASS = c_pdt_class.PDTCLASS_ID\n" +
            "WHERE \n" +
            "    EXTRACT(YEAR FROM c_weight_sheets.WEISHT_FULL_TM) = #{year}  and c_weight_sheets.DATA_FROM = #{dataFrom}\n" +
            "GROUP BY \n" +
            "    year,month, product_class, product\n" +
            "ORDER BY \n" +
            "    year,month, product_class, product;")
    List<MonthlySalesVo> monthlySales(int year, @Param("dataFrom") String dataFrom);

    Map<String, Object> getPlanWeight(@Param("cpyCo") String cpyCo, @Param("cpyChico") String cpyChico, @Param("pdtinfPdt") String pdtinfPdt, @Param("pdtPlanexBegin") String pdtPlanexBegin, @Param("pdtPlanexEnd") String pdtPlanexEnd);

    List<GetWeightByDayVo> getWeightByDay(@Param("s") GetWeightByDayVo getWeightByDayVo);

    //销售日报
    List<StatisticWeightSheetsDayVo> statisticWeightSheetsDay(@Param("s") StatisticWeightSheetsDayBo params);


/*
统计
 */

    List<StatisticsVo> statistics(@Param("s") StatisticsBo params);

    //出货类
    @Select("SELECT a.* FROM\n" +
            "(SELECT WEISHT_CO cpy,count(*) subTotal,WEISHT_CHI_CO cpyChi,WEISHT_PRODUCT from c_weight_sheets WHERE WEISHT_DELETED=0 and DATA_FROM=#{dataFrom} and WEISHT_FULL_TM BETWEEN #{yearStart} AND #{yearEnd}  GROUP BY WEISHT_CO , WEISHT_CHI_CO) a INNER JOIN c_products_info b ON a.WEISHT_PRODUCT=b.PDTINF_PDT WHERE b.PDTINF_STOCKCLS=0")
    List<CpyYearLogisticsVo> cpyYearLogistics(@Param("dataFrom") String dataFrom, String yearStart, String yearEnd);


    /**
     * 年度各公司花销情况
     */
    @Select("SELECT a.* FROM\n" +
            "(SELECT WEISHT_CO cpy,WEISHT_CHI_CO cpyChi,WEISHT_FULL_TM year,SUM(WEISHT_MONEY) subTotal ,WEISHT_PRODUCT from c_weight_sheets WHERE WEISHT_DELETED=0 and DATA_FROM=#{dataFrom} and WEISHT_FULL_TM BETWEEN #{yearStart} AND #{yearEnd}  GROUP BY WEISHT_CO , WEISHT_CHI_CO) a INNER JOIN c_products_info b ON a.WEISHT_PRODUCT=b.PDTINF_PDT WHERE b.PDTINF_STOCKCLS=0")
    List<CpyYearMoneyVo> cpyYearMoney(@Param("dataFrom") String dataFrom, String yearStart, String yearEnd);


    /**
     * 各货物年度出货量占比
     */
    @Select("SELECT a.* FROM\n" +
            "(SELECT WEISHT_PRODUCT product,SUM(WEISHT_NET_WGHT) weight from c_weight_sheets WHERE WEISHT_DELETED=0 and DATA_FROM=#{dataFrom} and WEISHT_FULL_TM BETWEEN #{yearStart} AND #{yearEnd}  GROUP BY WEISHT_PRODUCT) a INNER JOIN c_products_info b ON a.product=b.PDTINF_PDT WHERE b.PDTINF_STOCKCLS=0")
    List<ProductYearWeightVo> productYearWeight(@Param("dataFrom") String dataFrom, String yearStart, String yearEnd);

    //     List<StatisticsVo> financialStatistics
    List<StatisticsVo> financialStatistics(@Param("s") StatisticsBo params);

    //    List<StatisticsVo> financialStatistics2
    List<StatisticsVo> financialStatistics2(@Param("s") StatisticsBo params);

    //    financialStatistics3
    List<StatisticsVo> financialStatistics3(@Param("s") StatisticsBo params);

    //    financialStatistics4
    List<StatisticsVo> financialStatistics4(@Param("s") StatisticsBo params);


    /**
     * 各货物年度收款占比
     */
    @Select("SELECT a.* FROM\n" +
            "(SELECT WEISHT_PRODUCT product,SUM(WEISHT_MONEY) money from c_weight_sheets WHERE WEISHT_DELETED=0 and DATA_FROM=#{dataFrom} and WEISHT_FULL_TM BETWEEN #{yearStart} AND #{yearEnd}  GROUP BY WEISHT_PRODUCT) a INNER JOIN c_products_info b ON a.product=b.PDTINF_PDT WHERE b.PDTINF_STOCKCLS=0")
    List<ProductYearMoneyVo> productYearMoney(@Param("dataFrom") String dataFrom, String yearStart, String yearEnd);


    /**
     * 各公司得分
     */
    @Select("SELECT a.* FROM\n" +
            "(SELECT WEISHT_CO cpyCo,WEISHT_CHI_CO cpyChiCo,SUM(WEISHT_NET_WGHT) weight, SUM(WEISHT_MONEY) money ,WEISHT_PRODUCT from c_weight_sheets WHERE WEISHT_DELETED=0 and DATA_FROM=#{dataFrom} and WEISHT_FULL_TM BETWEEN  #{timeStart} AND #{timeEnd}  GROUP BY WEISHT_CO , WEISHT_CHI_CO) a INNER JOIN c_products_info b ON a.WEISHT_PRODUCT=b.PDTINF_PDT WHERE b.PDTINF_STOCKCLS=0")
    List<CpyScoreVo> cpyScore(@Param("dataFrom") String dataFrom, String timeStart, String timeEnd);


    List<CompanyWeightMoneyNumVo> dailyStatistics(@Param("s") CompanyWeightMoneyNumBo companyWeightMoneyNumBo);

    List<CompanyWeightMoneyNumVo> dailyStatisticsByCpy(@Param("s") CompanyWeightMoneyNumBo companyWeightMoneyNumBo);

    List<CompanyWeightMoneyNumVo> productWeightMoneyNumByOne(@Param("s") CompanyWeightMoneyNumBo companyWeightMoneyNumBo);


    List<GetCWeightSheetsVo> poundQueryForAdjustPrice(@Param("s") PoundQueryForAdjustPriceBo poundQueryForAdjustPriceBo,String dataFrom);

//    List<GetCWeightSheetsVo> poundQuery
//
//    ForAdjustPrice(@Param("s") CompanyWeightMoneyNumBo companyWeightMoneyNumBo);
    @Select("UPDATE CWeightSheets \n" +
            "SET WEISHT_PRICE = #{price},\n" +
            "    WEISHT_MONEY = #{price} * WEISHT_NET_WGHT\n" +
            "WHERE WEISHT_ID = #{weishtId} ;")
    void updateWeightSheet(String price,String weishtId);


    //年度环比报表

    List<YearOnYearReportVo> yearOnYearReport(@Param("s") Map map);
    List<YearOnYearReportVo> yearOnYearReportByPdt(@Param("s") Map map);

    List<SaleStatisticsListVo> saleList(SaleStatisticsBo saleStatisticsBo);
    List<SaleStatisticsListVo> saleListTotal(SaleStatisticsBo saleStatisticsBo);

    List<SaleReceiptsVo> saleReceipts(SaleStatisticsBo saleStatisticsBo);

    List<PdtSaleListVo> pdtSaleList(YearSaleExcelBo yearSaleExcelBo);

    List<PdtSaleListVo> pdtSaleTotal(YearSaleExcelBo yearSaleExcelBo);

    List<InvoiceStatisticsVo> exportInvoiceStatistics(InvoiceStatisticsBo invoiceStatisticsBo);
}
